with record_base as (
    select 
    problem_piece_id,create_by,create_by_name
    FROM 
    (
        select problem_piece_id,create_by,create_by_name,
        row_number() over(partition by problem_piece_id order by create_time desc) as rk
        from jms_dwd.dwd_problem_service_record_base_dt 
        where dt between date_add('{{ execution_date  | cst_ds }}',-29)  and '{{ execution_date  | cst_ds }}'
        and reply_type=2
    )t 
    where rk=1
),problem_detail as (
    SELECT
         problem.id
        ,problem.waybill_no       --单号
        ,problem.proble_type_id  --问题类型ID（异常件的操作类型）
        ,problem.proble_type_name         --问题类型名称（异常件操作类型）
        ,problem.proble_type_subject_id   --问题件一级类型id
        ,problem.proble_type_subject_code --问题件一级类型编码 
        ,problem.proble_type_subject_name --问题件一级类型名称
        ,problem.second_level_type_code   --问题件二级类型编码 
        ,problem.second_level_type_name   --问题件二级类型名称
        ,problem.proble_description   --问题件原因
        ,problem.receive_network_id   --接收网点id    
        ,problem.receive_network_code --接收网点code
        ,problem.receive_network_name --接收网点name
        ,problem.receive_network_proxy_code --接收网点代理区code
        ,problem.receive_network_proxy_name --接收网点代理区name
        ,problem.receive_network_mr_code  --接收网点管理大区编号
        ,problem.receive_network_mr_name  --接收网点管理大区编号
        ,problem.receive_network_parent_id  --接收方上级网点id
        ,problem.receive_network_parent_code --接收方上级网点code
        ,problem.receive_network_parent_name --接收方上级网点名称
        ,problem.registration_network_id    --登记网点id
        ,problem.registration_network_code  --登记网点code
        ,problem.registration_network_name  --登记网点name
        ,problem.reg_network_proxy_code   --登记网点code 
        ,problem.reg_network_proxy_name   --登记网点name
        ,problem.reg_network_mr_code      --登记网点管理大区编号
        ,problem.reg_network_mr_name      --登记网点管理大区名称
        ,problem.registration_network_parent_id  --登记网点的上级网点ID
        ,problem.registration_network_parent_code --登记网点的上级网点编码
        ,problem.registration_network_parent_name --登记网点的上级网点名称
        ,problem.create_by  --创建人编码 --登记人
        ,problem.create_by_code  --创建人编码 --登记人
        ,problem.create_by_name  --创建人编码 --登记人
        ,problem.customer_code  --客户编码
        ,problem.customer_name  --客户名称
        ,problem.sender_network_id --寄件网点id
        ,net1.code as sender_network_code --寄件网点编码
        ,problem.sender_network_name --寄件网点名称
        ,problem.sender_time --寄件时间
        ,record_base.create_by as reply_id--回复人
        ,record_base.create_by_name as reply_name--回复人
        ,'' as reply_code--回复人
        ,cast((unix_timestamp(problem.receiver_reply_time,'yyyy-MM-dd HH:mm:ss') - unix_timestamp(problem.create_time,'yyyy-MM-dd HH:mm:ss'))/60 as decimal(22,2)) as reply_duration 
        ,if(problem.proble_piece_status=5,problem.update_by_code,'') AS finish_user_code -- 完结操作人编码
        ,if(problem.proble_piece_status=5,problem.update_by_name,'')  AS finish_user --完结操作人
        ,if(problem.proble_piece_status=5,problem.update_time,'') as  finish_time--完结时间
        ,problem.order_source_code  --订单来源
        ,problem.order_source_name  --订单来源
        ,if(work.waybill_no is not null,1,0) as is_work_order --是否有工单
        ,way.dispatch_network_code
        ,way.dispatch_network_name
        ,problem.create_time
        ,problem.in_out_bound
        ,way.provider_desc
        ,way.city_desc
        ,way.area_desc
        ,problem.scan_time --问题件扫描时间
        ,problem.proble_piece_source --问题件来源
        ,problem.paths --路径
        ,problem.update_by_code as last_update_by_code --最后更新人
        ,problem.update_by_name as last_update_by_name --最后更新人name
        ,problem.update_by as last_update_by  --最后更新人id
        ,problem.update_time as  last_update_time --最后更新时间
        ,problem.proble_piece_status --问题件状态1：登记方已登记2：登记方已回复3：接收方已回复4:已转投诉件5:已完结
        ,problem.receiver_reply_time --接收方第一次登记时间
        ,problem.reply_duration as opt_duration--处理时长
        ,net1.agent_code as sender_agent_code --寄件代理区
        ,net1.agent_name as sender_agent_name
        ,net3.network_type  as reg_network_type --登记网点类型 网点类型:1-总部,2-代理区,3-加盟商,4-中心,5-集散点,6-网点
        ,row_number() over(partition by problem.waybill_no,problem.second_level_type_code,problem.receive_network_code,problem.registration_network_code,date(problem.create_time) order by problem.update_time desc) as is_repeat_flag
        ,need_reply --是否需要回复
        ,net4.network_type as rec_network_type --接受网点类型
        ,if(problem.receiver_reply_time is null,null, cast((unix_timestamp(problem.receiver_reply_time,'yyyy-MM-dd HH:mm:ss')-unix_timestamp(problem.create_time,'yyyy-MM-dd HH:mm:ss'))/60 as decimal(16,2) )) as reply_minutes  ---回复时长
        ,net3.fran_code as reg_fran_code  --登记网点加盟商code
        ,net3.fran_name as reg_fran_name  --登记网点加盟商
    from 
    (
    select *
    from jms_dwd.dwd_sqs_registration_problem_piece_base_dt detail
    where dt between date_add('{{ execution_date  | cst_ds }}',-29) and '{{ execution_date  | cst_ds }}'
    and registration_network_code is not null
   -- and (proble_type_subject_code='66' --不可抗力因素问题件
   -- or (proble_type_subject_code='29' and  second_level_type_code='29a')) --有发未到
    and date(create_time) between date_add('{{ execution_date  | cst_ds }}',-29) and '{{ execution_date  | cst_ds }}'
    )problem
    left join 
    (
        select w.waybill_no,w.dispatch_network_code ,w.dispatch_network_name ,net2.provider_desc,net2.city_desc,net2.area_desc
        from jms_dwd.dwd_yl_oms_oms_waybill_incre_dt w
        left join jms_dim.dim_network_whole_massage net2 on net2.code=w.dispatch_network_code
        where w.dt between date_add('{{ execution_date  | cst_ds }}',-29) and '{{ execution_date  | cst_ds }}'
    )way on way.waybill_no=problem.waybill_no
    left join 
    record_base on record_base.problem_piece_id=problem.id
    left join (
        select waybill_no
        from jms_dwd.dwd_work_order_new_base_dt 
        where dt between date_add('{{ execution_date  | cst_ds }}',-29) and '{{ execution_date  | cst_ds }}'
        group by waybill_no 
     )work on problem.waybill_no =work.waybill_no 
    left join jms_dim.dim_network_whole_massage net1 on net1.id=problem.sender_network_id  
    left join jms_dim.dim_network_whole_massage net3 on net3.code=problem.registration_network_code  
    left join jms_dim.dim_network_whole_massage net4 on net4.code=problem.receive_network_code 
) 
,work_project as (
    select 
    wp_create_time
    ,waybill_no
    ,accept_network_id
    ,accept_network_code
    ,accept_network_name            --受理网点
    ,wp_accept_by
    ,wp_accept_by_code
    ,wp_accept_by_name             --受理人
    ,response_network_id
    ,response_network_code
    ,response_network_name         --响应网点
    ,wp_update_time
    ,wp_update_by
    ,wp_update_by_code
    ,wp_update_by_name             --最后处理时间
    ,wk_duty_network_id
    ,wk_duty_network_code
    ,wk_duty_network_name
    from 
    (
    select create_time as wp_create_time,waybill_no
    ,accept_network_id,accept_network_code,accept_network_name --受理网点
    ,accept_by as wp_accept_by,accept_by_code as wp_accept_by_code,accept_by_name  as wp_accept_by_name--受理人
    ,response_network_id,response_network_code,response_network_name  --响应网点
    --,first_type_code,first_type_name,second_type_code,second_type_name
    ,update_time as wp_update_time,update_by as wp_update_by,update_by_code as wp_update_by_code,update_by_name as wp_update_by_name--最后处理时间
    ,duty_network_id  as wk_duty_network_id ,duty_network_code as wk_duty_network_code,duty_network_name as wk_duty_network_name  --责任网点name
    ,row_number() over(partition by waybill_no ,accept_network_code order by create_time) as rk
    from jms_dwd.dwd_work_order_new_base_dt 
    where dt between date_add('{{ execution_date  | cst_ds }}',-60) and '{{ execution_date  | cst_ds }}'
    and status =3 --已结案
    and first_type_code='21'
    and second_type_code='2101'
    )t where rk=1
),
interceptor_detail as (
     select --t-30到t-1 每天筛单网点
         network_code,province,city,area,area_type,dt
     from
         (
         select
         network_code
             ,province
             ,city
             ,area
             ,case when data_type ='area' and province is not null and city is not null and area is not null then 3
         when data_type ='area' and province is not null and city is not null and area is null then 2
         when data_type ='area' and province is not null and city is  null and area is null then 1
         else 0
         end as area_type
             ,data_type
             ,dt
         from jms_dim.dim_yl_oms_order_interceptor_base_dt
         where  data_type in ('network','the_third_segment_code','area')
         and is_enable=1
         and is_delete=0
         and abnormal_reason in ('自然灾害','疫情影响','疫情管控','政府管制','政府管控')
         --每天启用删除状态不一样 ，取最近30天数据
         and dt between  date_add('{{ execution_date  | cst_ds }}',-29)  and '{{ execution_date  | cst_ds }}' --每天筛单状态会变
         and ((effect_time_type=1 and ( date(effect_time_start)<=dt and date(effect_time_end)>=dt)) or effect_time_type=0  )
         -- and province='内蒙古自治区' and city='呼伦贝尔市'
         -- and network_code ='2776118'
         )m group by network_code,province,city,area,area_type,dt
),
abnormal_event as (
select --异常事件网点
network_code,effect_date
from 
(
    select 
    network_code,
    effect_time_start,
    effect_time_end,
    date_add(effect_time_start,(row_number() over(partition by network_code,effect_time_start,effect_time_end order by sp))-1) as effect_date
    from 
    (
        select 
        involved_org_code  as network_code,
        DATE_FORMAT(happen_time, 'yyyy-MM-dd') effect_time_start,
        NVL(DATE_FORMAT(end_time, 'yyyy-MM-dd'), '{{ execution_date  | cst_ds }}') effect_time_end ,
        SPACE(datediff(nvl(end_time,'{{ execution_date  | cst_ds }}'),happen_time)) as space_str
        from jms_dim.dim_abnormal_event_report_base
        --where scene_name in('政府行为','异常天气','自然灾害','传染疾病','交通管制','传染疾病','政府管制类事件','疫情管制')
        where ( (abnormal_type = '03' and scene_name in ('传染疾病','政府行为','交通管制','自然灾害','异常天气','场地变更','场地改造','外部侵害','支线交通事故','系统异常','设备异常（供应 商因素）'))
        or (abnormal_type = '14' and scene_name in ('疫情管制','政府管制类事件') )
        or (abnormal_type = '12' and scene_name in ('场地安全类自然灾害','外部人员侵害','火灾事故','设备故障')) 
        or (abnormal_type = '13' and scene_name='区域管制')
        or (abnormal_type = '15' and scene_name='IT类异常事件')
        or (abnormal_type = '16' and scene_name in('交通事故','供应商扣车','车辆故障','车辆涉水')))
        AND involved_org_code IS NOT NULL
        AND involved_org_code <> ''
        group by involved_org_code  ,happen_time,end_time
    )b LATERAL VIEW explode(split(space_str,'')) tmp as sp
)t group by network_code,effect_date
) 
insert overwrite table jms_dm.dm_sqs_registration_problem_detail_dt partition(dt)
select 
detail.id
,detail.create_time              --问题件登记时间
,detail.waybill_no               --单号
,detail.proble_type_id           --问题类型ID（异常件的操作类型）
,detail.proble_type_name         --问题类型名称（异常件操作类型）
,detail.proble_type_subject_id   --问题件一级类型id
,detail.proble_type_subject_code --问题件一级类型编码 
,detail.proble_type_subject_name --问题件一级类型名称
,detail.second_level_type_code   --问题件二级类型编码 
,detail.second_level_type_name   --问题件二级类型名称
,detail.proble_description   --问题件原因
,detail.receive_network_id   --接收网点id    
,detail.receive_network_code --接收网点code
,detail.receive_network_name --接收网点name
,detail.receive_network_proxy_code --接收网点代理区code
,detail.receive_network_proxy_name --接收网点代理区name
,detail.receive_network_mr_code  --接收网点管理大区编号
,detail.receive_network_mr_name  --接收网点管理大区编号
,detail.receive_network_parent_id  --接收方上级网点id
,detail.receive_network_parent_code --接收方上级网点code
,detail.receive_network_parent_name --接收方上级网点名称
,detail.registration_network_id    --登记网点id
,detail.registration_network_code  --登记网点code
,detail.registration_network_name  --登记网点name
,detail.reg_network_proxy_code   --登记网点code 
,detail.reg_network_proxy_name   --登记网点name
,detail.reg_network_mr_code      --登记网点管理大区编号
,detail.reg_network_mr_name      --登记网点管理大区名称
,detail.registration_network_parent_id  --登记网点的上级网点ID
,detail.registration_network_parent_code --登记网点的上级网点编码
,detail.registration_network_parent_name --登记网点的上级网点名称
,detail.create_by    as   create_by_id       --登记人
,detail.create_by_code               --创建人编码 --登记人
,detail.create_by_name               --创建人编码 --登记人
,detail.customer_code  --客户编码
,detail.customer_name  --客户名称
,detail.sender_network_id --寄件网点id
,detail.sender_network_code --寄件网点编码
,detail.sender_network_name --寄件网点名称
,detail.sender_time --寄件时间
,detail.reply_id--回复人
,detail.reply_name--回复人
,detail.reply_code--回复人
,detail.reply_duration --回复时长,分钟
,detail.finish_user_code -- 完结操作人编码
,detail.finish_user --完结操作人
,detail.finish_time--完结时间
,detail.order_source_code  --订单来源
,detail.order_source_name  --订单来源
,detail.dispatch_network_code --派件网点code
,detail.dispatch_network_name --派件网点name
,wk.wp_create_time      --工单登记时间
,wk.accept_network_id   --工单受理网点id
,wk.accept_network_code --工单受理网点code
,wk.accept_network_name --工单受理网点name
,wk.wp_accept_by        --工单受理人id
,wk.wp_accept_by_code   --工单受理人code
,wk.wp_accept_by_name   --工单受理人name        
,wk.response_network_id   --工单响应网点id
,wk.response_network_code --工单响应网点code
,wk.response_network_name --工单响应网点name
,wk.wp_update_time        --工单最后处理时间
,wk.wp_update_by          --工单最后处理人id
,wk.wp_update_by_code     --东单最后处理人code
,wk.wp_update_by_name     --最后处理时间
,wk.wk_duty_network_id    --责任网点id
,wk.wk_duty_network_code  --责任网点code
,wk.wk_duty_network_name  --责任网点name
,case when inter_r1.network_code is not null then 1
      when inter_d1.network_code is not null then 1
      when abn_d1.network_code is not null then 1
      when abn_r1.network_code is not null then 1
      when inter3.area_type is not null then 1
 else 0 end as is_report  --是否报备_不可抗力
,if( detail.create_time > wk.wp_create_time , 1 , null) as is_before_pro  --问题件前超时效催件_不可抗力
,if( detail.create_time < wk.wp_create_time , 1 , null) as is_after_pro   --问题件后超时效催件_不可抗力
,nosa.is_error --是否异常_有发未到
,nosa.is_error_type --异常类型——有发未到
,detail.in_out_bound -- 进出港字段 1 进港 2 出港
,date(detail.create_time) as date_time
,detail.scan_time --问题件扫描时间
,detail.proble_piece_source --问题件来源
,detail.paths --路径
,detail.last_update_by_code --最后更新人
,detail.last_update_by_name --最后更新人name
,detail.last_update_by  --最后更新人
,detail.proble_piece_status --问题件状态1：登记方已登记2：登记方已回复3：接收方已回复4:已转投诉件5:已完结
,detail.receiver_reply_time --接收方第一次登记时间
,detail.opt_duration--处理时长
,detail.sender_agent_code --寄件代理区
,detail.sender_agent_name --寄件代理区名称
,detail.reg_network_type --登记网点类型 网点类型:1-总部,2-代理区,3-加盟商,4-中心,5-集散点,6-网点
,detail.is_repeat_flag --是否去重明细（1是）
,detail.is_work_order --是否工单（1是0否）
,detail.need_reply --是否需要回复
,detail.last_update_time
,detail.rec_network_type  --接受网点类型
,detail.reply_minutes     --回复时长，单位小时
,detail.reg_fran_code  --登记网点加盟商code
,detail.reg_fran_name  --登记网点加盟商
,date(detail.create_time) as dt
from 
problem_detail detail
left join  --问题件登记网点+筛单
 --筛单为网点和三段码类型的数据
interceptor_detail inter_r1 on inter_r1.network_code = detail.registration_network_code and detail.proble_type_subject_code='66' and date(detail.create_time)=inter_r1.dt and inter_r1.area_type=0
left join  --问题件派件网点+筛单
 --筛单为网点和三段码类型的数据
interceptor_detail inter_d1 on inter_d1.network_code = detail.dispatch_network_code and detail.dispatch_network_code is not null and detail.proble_type_subject_code='66' and date(detail.create_time)=inter_d1.dt and inter_d1.area_type=0
left join  --剔除筛单数据为区域类型
interceptor_detail inter3 on date(detail.create_time)=inter3.dt  and ((inter3.province = detail.provider_desc and inter3.area_type = 1)
         or (inter3.province = detail.provider_desc and inter3.city = detail.city_desc and inter3.area_type = 2)
         or (inter3.province = detail.provider_desc and inter3.city = detail.city_desc and inter3.area = detail.area_desc and inter3.area_type = 3))
left join  --问题件派件网点+异常时间网点
abnormal_event abn_d1 on abn_d1.network_code = detail.dispatch_network_code and detail.dispatch_network_code is not null and detail.proble_type_subject_code='66' 
         and abn_d1.effect_date = date(detail.create_time) 
left join  --问题件登记网点+异常时间网点
abnormal_event abn_r1 on abn_r1.network_code = detail.registration_network_code and detail.proble_type_subject_code='66' 
         and abn_r1.effect_date = date(detail.create_time) 
left join  --超时效催件工单
work_project wk on detail.waybill_no=wk.waybill_no and wk.accept_network_code=detail.registration_network_code 
left join --有发未到
(
    select *
    --,first_type_code,first_type_name,second_type_code,second_type_name
    from jms_dm.dm_send_no_arrival_detail_dt
    where dt between date_add('{{ execution_date  | cst_ds }}',-29) and '{{ execution_date  | cst_ds }}'
)nosa on detail.waybill_no=nosa.waybill_no and detail.registration_network_code=nosa.registration_network_code 
     and (detail.proble_type_subject_code='29' and  detail.second_level_type_code='29a')
     and nosa.id=detail.id
distribute by dt,abs(hash(id)%48);